﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;

/*
 * List of needed methods: (delete if you have implemented it)
 * 
*/

namespace LLServer.Data
{
    public class Data
    {
        private string server;
        private string port;
        private string database;
        private string uid;
        private string password;
        private MySqlConnection connection;

        //private List<World> worlds;

        public Data()
        {
            
        }

        public void initialize()
        {
            Console.WriteLine("Connecting to database.");
            
            server = "sql.landlubbers.nazwa.pl";
            port = "3307";
            database = "landlubbers";
            uid = "landlubbers";
            password = "LandLubbers352";

            string connectionString = "SERVER=" + server + ";" + "PORT=" + port + ";" + "DATABASE=" + database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";
            Console.WriteLine(connectionString);
            connection = new MySqlConnection(connectionString);
        }


        #region connection
        private bool openConnection()
        {
            Console.Write("Opening connection... ");
            try
            {
                connection.Open();
                Console.WriteLine("Done.");
                return true;
            }
            catch (MySqlException ex)
            {
                Console.WriteLine("Error " + ex.Number + ": " + ex.Message);
                return false;
            }
            catch (InvalidOperationException ex)
            {
                Console.WriteLine("Error: " + ex.Message);
                return false;
            }
        }

        private bool closeConnection()
        {
            Console.Write("Closing connection... ");
            try
            {
                connection.Close();
                Console.WriteLine("Done.");
                return true;
            }
            catch (MySqlException ex)
            {
                Console.WriteLine("Error " + ex.Number + ": " + ex.Message);
                return false;
            }
            catch (InvalidOperationException ex)
            {
                Console.WriteLine("Error: " + ex.Message);
                return false;
            }
        }
        #endregion

        #region user
        public bool Login(Client client)
        {
            Console.WriteLine("Attempting to login (username = " + client.Username + ", password = " + client.Password + ").");
            bool exist = false;
            if (this.openConnection())
            {
                string query = "SELECT users.user_id FROM users WHERE users.username = '" + client.Username + "' AND users.password = '" + client.Password + "';";
                Console.WriteLine(query);
                MySqlCommand cmd = new MySqlCommand(query, connection);

                try
                {
                    MySqlDataReader dataReader = cmd.ExecuteReader();

                    if (dataReader.HasRows)
                        exist = true;

                    dataReader.Close();
                    this.closeConnection();
                }
                catch (MySqlException ex)
                {
                    Console.WriteLine("Error " + ex.Number + ": " + ex.Message);
                    this.closeConnection();
                }
            }
            return exist;
        }

        public int SignUp(User user, Captain captain)
        {
            Console.WriteLine("Attempting to sign up (username = " + user.Username + ", password = " + user.Password + ", fraction = " + captain.Fraction_id + "captain name = " + captain.Name + ").");
            int result = -1;
            if (GetUserIdByUsername(user.Username) <= 0) // if username is available
            {
                if (AddCaptain(captain.Name) > 0)
                {
                    captain.Id = GetCaptainIdByName(captain.Name);

                    if (this.openConnection() == true)
                    {
                        string query = "INSERT INTO `landlubbers`.`users` (`username`,`password`,`captain_id`,`fraction_id`,`world_id`) VALUES ('" +
                            user.Username + "', '" + user.Password + "', " + captain.Id + ", " + captain.Fraction_id + ", " + captain.World + ");";
                        Console.WriteLine(query);
                        MySqlCommand cmd = new MySqlCommand();
                        cmd.Connection = connection;
                        cmd.CommandText = query;

                        try
                        {
                            result = cmd.ExecuteNonQuery();
                        }
                        catch (MySqlException ex)
                        {
                            Console.WriteLine("Error " + ex.Number + ": " + ex.Message);
                        }
                        finally
                        {
                            if (connection != null)
                                this.closeConnection();
                        }
                    }
                }
            }
            return result;
        }

        public int GetUserIdByUsername(string username)
        {
            int user_id = -1;
            Console.WriteLine("Attemtping to get user id (username = " + username + ").");
            if (this.openConnection() == true)
            {
                string query = "SELECT users.user_id FROM users WHERE users.username='" + username + "';";
                Console.WriteLine(query);
                MySqlCommand cmd = new MySqlCommand(query, connection);

                try
                {
                    MySqlDataReader dataReader = cmd.ExecuteReader();

                    while (dataReader.Read())
                    {
                        user_id = (int)dataReader["user_id"];
                    }

                    dataReader.Close();
                    this.closeConnection();
                    return user_id;
                }
                catch (MySqlException ex)
                {
                    Console.WriteLine("Error " + ex.Number + ": " + ex.Message);
                    this.closeConnection();
                    return -1;
                }
            }
            else
                return -1;
        }
        #endregion

        #region world
        public World GetWorld(int world_id)
        {
            Console.WriteLine("Attempting to get world (world_id = " + world_id + ").");
            if (this.openConnection() == true)
            {
                World world = new World();

                string query = "SELECT * FROM worlds WHERE worlds.world_id=" + world_id + ";";
                Console.WriteLine(query);
                MySqlCommand cmd = new MySqlCommand(query, connection);

                try
                {
                    MySqlDataReader dataReader = cmd.ExecuteReader();

                    while (dataReader.Read())
                    {
                        world.Id = (int)dataReader["world_id"];
                        world.Name = (string)dataReader["name"];
                        world.Description = (string)dataReader["description"];
                    }

                    dataReader.Close();
                    this.closeConnection();
                    return world;
                }
                catch (MySqlException ex)
                {
                    Console.WriteLine("Error " + ex.Number + ": " + ex.Message);
                    this.closeConnection();
                    return null;
                }
            }
            return null;
        }
        #endregion

        #region captain
        public int AddCaptain(string captain_name)
        {
            int result = -1;
            Console.WriteLine("Attempting to add captain (captain name = " + captain_name + ").");
            if (GetCaptainIdByName(captain_name) > 0)
                return result;

            if (this.openConnection() == true)
            {
                string query;
                query = "INSERT INTO captains VALUES ('" + captain_name + "');";
                Console.WriteLine(query);
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = connection;
                cmd.CommandText = query;

                try
                {
                    result = cmd.ExecuteNonQuery();
                }
                catch (MySqlException ex)
                {
                    Console.WriteLine("Error " + ex.Number + ": " + ex.Message);
                }
                finally
                {
                    if (connection != null)
                        this.closeConnection();
                }
            }
            result = AddCaptainStats(captain_name);
            return result;
        }

        public Captain GetCaptainByUsername(string username)
        {
            Console.WriteLine("Attempting to get captain (username = " + username + ").");
            
            Captain capt = new Captain();
            capt.Id = GetCaptainIdByUsername(username);
            if (this.openConnection())
            {
                string query = "SELECT captains.captain_id, captains.name, captains.level, captains.experience, captains.honor, users.fraction_id FROM captains, users WHERE captains.captain_id=users.captain_id AND captains.captain_id=" + capt.Id + ";";
                Console.WriteLine(query);
                MySqlCommand cmd = new MySqlCommand(query, connection);

                try
                {
                    MySqlDataReader dataReader = cmd.ExecuteReader();
                    
                    if (dataReader.Read())
                    {
                        //capt.Id = (int)dataReader["captain_id"];
                        capt.Name = (string)dataReader["name"];
                        capt.Level = (int)dataReader["level"];
                        capt.Experience = (int)dataReader["experience"];
                        capt.Honor = (int)dataReader["honor"];
                        capt.Fraction_id = (int)dataReader["fraction_id"];
                    }
                    
                    dataReader.Close();
                    this.closeConnection();
                    return capt;
                }
                catch (MySqlException ex)
                {
                    Console.WriteLine("Error " + ex.Number + ": " + ex.Message);
                    this.closeConnection();
                    return null;
                }
            }
            return null;
        }

        public Captain GetCaptainById(int captain_id)
        {
            Console.WriteLine("Attempting to get captain (captain id = " + captain_id + ").");
            if (this.openConnection())
            {
                Captain capt = new Captain();
                string query = "SELECT * FROM captains WHERE captains.captain_id=" + captain_id + ";";
                Console.WriteLine(query);
                MySqlCommand cmd = new MySqlCommand(query, connection);

                try
                {
                    MySqlDataReader dataReader = cmd.ExecuteReader();

                    while (dataReader.Read())
                    {
                        capt.Name = (string)dataReader["name"];
                        capt.Level = (int)dataReader["level"];
                        capt.Experience = (int)dataReader["experience"];
                        capt.Honor = (int)dataReader["honor"];
                    }

                    dataReader.Close();
                    this.closeConnection();
                    return capt;
                }
                catch (MySqlException ex)
                {
                    Console.WriteLine("Error " + ex.Number + ": " + ex.Message);
                    this.closeConnection();
                    return null;
                }
            }
            return null;
        }

        public int GetCaptainIdByUsername(string username)
        {
            int capt_id = -1;
            Console.WriteLine("Attemtping to get captain id (username = " + username + ").");

            if (this.openConnection() == true)
            {
                string query = "SELECT users.captain_id FROM users where users.username='" + username + "';";
                Console.WriteLine(query);
                MySqlCommand cmd = new MySqlCommand(query, connection);

                try
                {
                    MySqlDataReader dataReader = cmd.ExecuteReader();

                    if (dataReader.Read())
                    {
                        capt_id = (int)dataReader["captain_id"];
                    }
                    
                    dataReader.Close();
                    this.closeConnection();
                    return capt_id;
                }
                catch (MySqlException ex)
                {
                    Console.WriteLine("Error " + ex.Number + ": " + ex.Message);
                    this.closeConnection();
                    return -2;
                }
            }
            else
                return -3;
        }

        public int GetCaptainIdByName(string captainName)
        {
            int capt_id = -1;
            Console.WriteLine("Attemtping to get captain id (captain name = " + captainName + ").");

            if (this.openConnection() == true)
            {
                string query = "SELECT captains.captain_id FROM captains where captains.name='" + captainName + "';";
                Console.WriteLine(query);
                MySqlCommand cmd = new MySqlCommand(query, connection);

                try
                {
                    MySqlDataReader dataReader = cmd.ExecuteReader();

                    while (dataReader.Read())
                    {
                        capt_id = (int)dataReader["captain_id"];
                    }

                    dataReader.Close();
                    this.closeConnection();
                    return capt_id;
                }
                catch (MySqlException ex)
                {
                    Console.WriteLine("Error " + ex.Number + ": " + ex.Message);
                    this.closeConnection();
                    return capt_id;
                }
            }
            else
                return capt_id;

        }


        public List<Captain> GetAllCaptains()
        {
            List<Captain> captains = new List<Captain>();

            if (this.openConnection())
            {
                string query = "SELECT * FROM captains WHERE captains.captain_id <> 1;";
                MySqlCommand cmd = new MySqlCommand(query, connection);

                try
                {
                    MySqlDataReader dataReader = cmd.ExecuteReader();

                    while (dataReader.Read())
                    {
                        Captain capt = new Captain();
                        capt.Id = (int)dataReader["captain_id"];
                        capt.Name = (string)dataReader["name"];
                        capt.Level = (int)dataReader["level"];
                        capt.Experience = (int)dataReader["experience"];
                        capt.Honor = (int)dataReader["honor"];

                        captains.Add(capt);
                    }
                    dataReader.Close();
                    this.closeConnection();
                }
                catch(MySqlException ex)
                {
                    Console.WriteLine("Error " + ex.Number + ": " + ex.Message);
                    this.closeConnection();
                    return captains;
                }
            }

            return captains;
        }

        #endregion

        #region captain stats
        public int AddCaptainStats(string captain_name)
        {
            int result = -1;
            Console.WriteLine("Attempting to add captain stats (captain name = " + captain_name + ").");
            if (this.openConnection() == true)
            {
                int captain_id = GetCaptainIdByName(captain_name);
                if (captain_id >= 0)
                    return result;

                string query;
                query = "INSERT INTO captains_stats VALUES ('" + captain_id + "');";
                Console.WriteLine(query);
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = connection;
                cmd.CommandText = query;

                try
                {
                    result = cmd.ExecuteNonQuery();
                }
                catch (MySqlException ex)
                {
                    Console.WriteLine("Error " + ex.Number + ": " + ex.Message);
                }
                finally
                {
                    if (connection != null)
                        this.closeConnection();
                }
            }
            return result;
        }

        public CptStats GetCptStats(string captain_name)
        {
            Console.WriteLine("Attempting to get captain stats (captain name = " + captain_name + ").");
            if (this.openConnection())
            {
                int captain_id = GetCaptainIdByName(captain_name);
                CptStats cpt_stats = new CptStats();
                string query = "SELECT * FROM captain_stats WHERE captain_stats.captain_id =" + captain_id + ";";
                Console.WriteLine(query);
                MySqlCommand cmd = new MySqlCommand(query, connection);

                try
                {
                    MySqlDataReader dataReader = cmd.ExecuteReader();

                    while (dataReader.Read())
                    {
                        cpt_stats.Battles_won = (int)dataReader["battles_won"];
                        cpt_stats.Battles_lost = (int)dataReader["battles_lost"];
                    }

                    dataReader.Close();
                    this.closeConnection();
                    return cpt_stats;
                }
                catch (MySqlException ex)
                {
                    Console.WriteLine("Error " + ex.Number + ": " + ex.Message);
                    this.closeConnection();
                    return null;
                }
            }
            return null;
        }

        public List<CptStats> GetAllCaptainStats()
        {
            List<CptStats> cStats = new List<CptStats>();

            Console.WriteLine("Attempting to get all captain stats");
            if (this.openConnection())
            {
                string query = "SELECT captains.name, captain_stats.battles_won, captain_stats.battles_lost FROM captain_stats INNER JOIN captains ON captain_stats.captain_id = captains.captain_id WHERE captains.captain_id <> 1;";
                Console.WriteLine(query);
                MySqlCommand cmd = new MySqlCommand(query, connection);

                try
                {
                    MySqlDataReader dataReader = cmd.ExecuteReader();

                    while (dataReader.Read())
                    {
                        CptStats cpt_stats = new CptStats();
                        cpt_stats.Captain_Name = (string)dataReader["name"];
                        cpt_stats.Battles_won = (int)dataReader["battles_won"];
                        cpt_stats.Battles_lost = (int)dataReader["battles_lost"];
                        cStats.Add(cpt_stats);
                    }

                    dataReader.Close();
                    this.closeConnection();
                }
                catch (MySqlException ex)
                {
                    Console.WriteLine("Error " + ex.Number + ": " + ex.Message);
                    this.closeConnection();
                    return null;
                }
            }

            return cStats;
        }
        #endregion

        #region level and rank
        public string GetRankName(int rank, int fraction_id)
        {
            Console.WriteLine("Attempting to get rank (rank id = " + rank + ", fraction id = " + fraction_id + ").");
            if (this.openConnection())
            {
                Captain capt = new Captain();
                string query = "SELECT * FROM ranks WHERE ranks.rank =" + rank + " AND ranks.fraction_id = " + fraction_id + ";";
                Console.WriteLine(query);
                MySqlCommand cmd = new MySqlCommand(query, connection);

                try
                {
                    string rank_name = "";
                    MySqlDataReader dataReader = cmd.ExecuteReader();

                    while (dataReader.Read())
                    {
                        rank_name = (string)dataReader["name"];
                    }

                    dataReader.Close();
                    this.closeConnection();
                    return rank_name;
                }
                catch (MySqlException ex)
                {
                    Console.WriteLine("Error " + ex.Number + ": " + ex.Message);
                    this.closeConnection();
                    return null;
                }
            }
            return null;
        }

        public void GrantNextLevel(int captain_id, int fraction_id, int current_level)
        {
            Console.WriteLine("Attempting to grant next level (captain id = " + captain_id + ", current level = " + current_level + ").");
            if (this.openConnection())
            {
                string query;
                MySqlCommand cmd;
                int limit = 0, experience = 0;

                query = "SELECT * FROM levels WHERE levels.level = " + current_level + " AND levels.fraction_id = " + fraction_id + ";";
                Console.WriteLine(query);
                cmd = new MySqlCommand(query, connection);

                try
                {
                    MySqlDataReader dataReader = cmd.ExecuteReader();

                    while (dataReader.Read())
                    {
                        limit = (int)dataReader["limit"];
                    }

                    dataReader.Close();
                }
                catch (MySqlException ex)
                {
                    Console.WriteLine("Error " + ex.Number + ": " + ex.Message);
                }

                query = "SELECT * FROM captains WHERE captain.captain_id = " + captain_id + ";";
                Console.WriteLine(query);
                cmd = new MySqlCommand(query, connection);

                try
                {
                    MySqlDataReader dataReader = cmd.ExecuteReader();

                    while (dataReader.Read())
                    {
                        experience = (int)dataReader["experience"];
                    }

                    dataReader.Close();
                }
                catch (MySqlException ex)
                {
                    Console.WriteLine("Error " + ex.Number + ": " + ex.Message);
                }

                if (experience >= limit)
                {
                    int temp = experience - limit;
                    List<string> queries = new List<string>();
                    queries.Add("UPDATE captains SET captains.level = captains.level + 1 WHERE captains.captain_id = " + captain_id + ";");
                    queries.Add("UPDATE captains SET captains.experience = " + temp + " WHERE captains.captain_id = " + captain_id + ";");

                    foreach(string temp_query in queries)
                    {
                        cmd = new MySqlCommand(temp_query, connection);
                        Console.WriteLine(temp_query);
                        cmd.Connection = connection;
                        cmd.CommandText = temp_query;

                        try
                        {
                            cmd.ExecuteNonQuery();
                        }
                        catch (MySqlException ex)
                        {
                            Console.WriteLine("Error " + ex.Number + ": " + ex.Message);
                        }
                    }

                    if (connection != null)
                        this.closeConnection();
                }
            }
        }

        public void GrantNextRank(int captain_id, int fraction_id, int current_rank)
        {
            Console.WriteLine("Attempting to grant next rank (captain id = " + captain_id + ", current level = " + current_rank + ").");
            if (this.openConnection())
            {
                string query;
                MySqlCommand cmd;
                int limit = 0, honor = 0;

                query = "SELECT * FROM ranks WHERE ranks.rank = " + current_rank + " AND ranks.fraction_id = " + fraction_id + ";";
                Console.WriteLine(query);
                cmd = new MySqlCommand(query, connection);

                try
                {
                    MySqlDataReader dataReader = cmd.ExecuteReader();

                    while (dataReader.Read())
                    {
                        limit = (int)dataReader["limit"];
                    }

                    dataReader.Close();
                }
                catch (MySqlException ex)
                {
                    Console.WriteLine("Error " + ex.Number + ": " + ex.Message);
                }

                query = "SELECT * FROM captains WHERE captain.captain_id = " + captain_id + ";";
                Console.WriteLine(query);
                cmd = new MySqlCommand(query, connection);

                try
                {
                    MySqlDataReader dataReader = cmd.ExecuteReader();

                    while (dataReader.Read())
                    {
                        honor = (int)dataReader["honor"];
                    }

                    dataReader.Close();
                }
                catch (MySqlException ex)
                {
                    Console.WriteLine("Error " + ex.Number + ": " + ex.Message);
                }

                if (honor >= limit)
                {
                    int temp = honor - limit;
                    List<string> queries = new List<string>();
                    queries.Add("UPDATE captains SET captains.rank = captains.rank + 1 WHERE captains.captain_id = " + captain_id + ";");
                    queries.Add("UPDATE captains SET captains.honor = " + temp + " WHERE captains.captain_id = " + captain_id + ";");

                    foreach(string temp_query in queries)
                    {
                        cmd = new MySqlCommand(temp_query, connection);
                        Console.WriteLine(temp_query);
                        cmd.Connection = connection;
                        cmd.CommandText = temp_query;

                        try
                        {
                            cmd.ExecuteNonQuery();
                        }
                        catch (MySqlException ex)
                        {
                            Console.WriteLine("Error " + ex.Number + ": " + ex.Message);
                        }
                    }

                    if (connection != null)
                        this.closeConnection();
                }
            }
        }
        #endregion

        #region battle report
        public int AddBattleReport(BattleReport battle_report)
        {
            int result = -1;
            Console.WriteLine("Attemtping to add battle report.");
            if (this.openConnection() == true)
            {
                string query = "INSERT INTO battle_reports (`captain1_id`,`captain2_id`,`captain1_fraction_id`,`captain2_fraction_id`,`winner_id`,`start_date`,`end_date`,`winner_experience`,`loser_experience`,`winner_honor`,`loser_honor`) VALUES ('" +
                            battle_report.Cpt1_id + "', '" + battle_report.Cpt2_id + "', '" + battle_report.Cpt1_fraction_id + "', '" + battle_report.Cpt2_fraction_id + "', '" + battle_report.Winner_id + "', '" + battle_report.Start_date + "', '" + battle_report.End_date + "', '" + battle_report.Winner_experience + "', '" + battle_report.Loser_experience + "', '" + battle_report.Winner_honor + "', '" + battle_report.Loser_honor + "');";
                Console.WriteLine(query);
                MySqlCommand cmd = new MySqlCommand();

                cmd.Connection = connection;
                cmd.CommandText = query;

                try
                {
                    result = cmd.ExecuteNonQuery();
                }
                catch (MySqlException ex)
                {
                    Console.WriteLine("Error " + ex.Number + ": " + ex.Message);
                }
                finally
                {
                    if (connection != null)
                        this.closeConnection();
                }
            }
            result = UpdateStats(battle_report);
            return result;
        }

        public BattleReport GetBattleReport(int battle_report_id)
        {
            Console.WriteLine("Attempting to get battle report (id = " + battle_report_id + ").");
            if (this.openConnection())
            {
                BattleReport battle_report = new BattleReport();
                string query = "SELECT * FROM battle_reports WHERE battle_reports.battle_report_id =" + battle_report_id + ";";
                Console.WriteLine(query);
                MySqlCommand cmd = new MySqlCommand(query, connection);

                try
                {
                    MySqlDataReader dataReader = cmd.ExecuteReader();

                    while (dataReader.Read())
                    {
                        battle_report.Cpt1_id = (int)dataReader["captain1_id"];
                        battle_report.Cpt1_id = (int)dataReader["captain2_id"];
                        battle_report.Cpt1_fraction_id = (int)dataReader["captain1_fraction_id"];
                        battle_report.Cpt2_fraction_id = (int)dataReader["captain2_fraction_id"];
                        battle_report.Winner_id = (int)dataReader["winner_id"];
                        battle_report.Start_date = (DateTime)dataReader["start_date"];
                        battle_report.End_date = (DateTime)dataReader["end_date"];
                        battle_report.Winner_experience = (int)dataReader["winner_experience"];
                        battle_report.Loser_experience = (int)dataReader["loser_experience"];
                        battle_report.Winner_honor = (int)dataReader["winner_honor"];
                        battle_report.Loser_honor = (int)dataReader["loser_honor"];
                    }

                    dataReader.Close();
                    this.closeConnection();
                    return battle_report;
                }
                catch (MySqlException ex)
                {
                    Console.WriteLine("Error " + ex.Number + ": " + ex.Message);
                    this.closeConnection();
                    return null;
                }
            }
            return null;
        }

        public List<BattleReport> GetAllBattleReports()
        {
            List<BattleReport> bReports = new List<BattleReport>();

            Console.WriteLine("Attempting to get all battle reports");
            if (this.openConnection())
            {
                string query = "SELECT * FROM battle_reports;";
                Console.WriteLine(query);
                MySqlCommand cmd = new MySqlCommand(query, connection);

                try
                {
                    MySqlDataReader dataReader = cmd.ExecuteReader();

                    while (dataReader.Read())
                    {
                        BattleReport battle_report = new BattleReport();
                        battle_report.Cpt1_id = (int)dataReader["captain1_id"];
                        battle_report.Cpt2_id = (int)dataReader["captain2_id"];
                        battle_report.Cpt1_fraction_id = (sbyte)dataReader["captain1_fraction_id"];
                        battle_report.Cpt2_fraction_id = (sbyte)dataReader["captain2_fraction_id"];
                        battle_report.Winner_id = (int)dataReader["winner_id"];
                        //battle_report.Start_date = (DateTime)dataReader["start_date"];
                       // battle_report.End_date = (DateTime)dataReader["end_date"];
                        battle_report.Winner_experience = (int)dataReader["winner_experience"];
                        battle_report.Loser_experience = (int)dataReader["loser_experience"];
                        battle_report.Winner_honor = (int)dataReader["winner_honor"];
                        battle_report.Loser_honor = (int)dataReader["loser_honor"];

                        bReports.Add(battle_report);
                    }

                    dataReader.Close();
                    this.closeConnection();
                }
                catch (MySqlException ex)
                {
                    Console.WriteLine("Error " + ex.Number + ": " + ex.Message);
                    this.closeConnection();
                    return null;
                }
            }
            return bReports;
        }

        public int UpdateStats(BattleReport battle_report)
        {
            Console.WriteLine("Attempting to update stats.");
            int result = -1;
            if (this.openConnection() == true)
            {
                List<string> queries = new List<string>();
                int winner_id = 0, loser_id = 0, winner_fraction_id = 0, loser_fraction_id = 0;

                if (battle_report.Winner_id == battle_report.Cpt1_id)
                {
                    winner_id = battle_report.Cpt1_id;
                    winner_fraction_id = battle_report.Cpt1_fraction_id;
                    loser_id = battle_report.Cpt2_id;
                    loser_fraction_id = battle_report.Cpt2_fraction_id;
                }
                else
                {
                    winner_id = battle_report.Cpt2_id;
                    winner_fraction_id = battle_report.Cpt2_fraction_id;
                    loser_id = battle_report.Cpt1_id;
                    loser_fraction_id = battle_report.Cpt1_fraction_id;
                }

                queries.Add("UPDATE captains SET captains.experience = captains.experience + " + battle_report.Winner_experience + " WHERE captains.captain_id = " + winner_id + ";");
                queries.Add("UPDATE captains SET captains.experience = captains.experience + " + battle_report.Loser_experience + " WHERE captains.captain_id = " + loser_id + ";");
                queries.Add("UPDATE captains SET captains.honor = captains.honor + " + battle_report.Winner_honor + " WHERE captains.captain_id = " + winner_id + ";");
                queries.Add("UPDATE captains SET captains.honor = captains.honor + " + battle_report.Loser_honor + " WHERE captains.captain_id = " + loser_id + ";");
                queries.Add("UPDATE captain_stats SET captain_stats.battles_won = captain_stats.battles_won + 1 WHERE captain_stats.captain_id = " + winner_id + ";");
                queries.Add("UPDATE captain_stats SET captain_stats.battles_lost = captain_stats.battles_lost + 1 WHERE captain_stats.captain_id = " + loser_id + ";");
                queries.Add("UPDATE fraction_stats SET fraction_stats.battles_won = fraction_stats.battles_won + 1 WHERE fraction_stats.fraction_id = " + winner_fraction_id + ";");
                queries.Add("UPDATE fraction_stats SET fraction_stats.battles_lost = fraction_stats.battles_lost + 1 WHERE fraction_stats.fraction_id = " + loser_fraction_id + ";");

                foreach (string query in queries)
                {
                    MySqlCommand cmd = new MySqlCommand();
                    Console.WriteLine(query);
                    cmd.Connection = connection;
                    cmd.CommandText = query;

                    try
                    {
                        result = cmd.ExecuteNonQuery();
                    }
                    catch (MySqlException ex)
                    {
                        Console.WriteLine("Error " + ex.Number + ": " + ex.Message);
                    }
                }

                if (connection != null)
                    this.closeConnection();
            }
            return result;
        }
        #endregion

        public List<FractionStats> GetAllFractionStats()
        {
            List<FractionStats> fStats = new List<FractionStats>();

            Console.WriteLine("Attempting to get all fraction stats");
            if (this.openConnection())
            {
                string query = "SELECT fractions.name, fraction_stats.battles_won, fraction_stats.battles_lost FROM fraction_stats INNER JOIN fractions ON fraction_stats.fraction_id = fractions.fraction_id;";
                Console.WriteLine(query);
                MySqlCommand cmd = new MySqlCommand(query, connection);

                try
                {
                    MySqlDataReader dataReader = cmd.ExecuteReader();

                    while (dataReader.Read())
                    {
                        FractionStats f_stat = new FractionStats();
                        f_stat.Fraction_name = (string)dataReader["name"];
                        f_stat.Battles_won = (int)dataReader["battles_won"];
                        f_stat.Battles_lost = (int)dataReader["battles_lost"];
                        fStats.Add(f_stat);
                    }

                    dataReader.Close();
                    this.closeConnection();
                }
                catch (MySqlException ex)
                {
                    Console.WriteLine("Error " + ex.Number + ": " + ex.Message);
                    this.closeConnection();
                    return null;
                }
            }

            return fStats;
        }
    }
}
